跳到主要内容

MySQL 学习（21）数据库优化

为什么要优化​

  • 系统的吞吐量 瓶颈往往出现在数据库的访问速度上
  • 随着应用程序的运行，数据库的中的数据会越来越多，处理时间会相应变慢
  • 数据是存放在磁盘上的，读写速度无法和内存相比

优化原则：减少系统瓶颈，减少资源占用，增加系统的反应速度。

选择合适的 CPU​

用户首先需要清楚当前数据库的应用类型。一般而言，可分为两大类: OLTP（Online Transaction Processing，在线事务处理）和 OLAP（Online Analytical Processing，在线分析处理）。

这是两种截然不同的数据库应用。

  • OLAP 多用在数据仓库或数据集市中，一般需要执行复杂的 SQL 语句来进行查询；
  • OLTP 多用在日常的事物处理应用中，如银行交易、在线商品交易、Blog、网络游戏等应用。相对于 OLAP，数据库的容量较小。

InnoDB 存储引擎一般都应用于 OLTP 的数据库应用，这种应用的特点如下：

  • 用户操作的并发量大
  • 事务处理的时间--般比较短
  • 查询的语句较为简单，一般都走索引
  • 复杂的查询较少

可以看出，OLTP 的数据库应用本身对 CPU 的要求并不是很高，因为复杂的查询可能需要执行比较、排序、连接等非常耗 CPU 的操作，这些操作在 OLTP 的数据库应用中较少发生。

因此，可以说 OLAP 是 CPU 密集型的操作，而 OLTP 是 IO 密集型的操作。

建议在采购设备时，将更多的注意力放在 提高 IO 的配置上。此外，为了获得更多内存的支持，用户采购的 CPU 必须支持 64 位，否则无法支持 64 位操作系统的安装。

从 InnoDB 存储引擎的设计架构上来看，其主要的后台操作都是在一个单独的 master thread 中完成的，因此并不能很好地支持多核的应用。当然，开源社区已经通过多种方法来改变这种局面，而 InnoDB1.0 版本在各种测试下已经显示出对多核 CPU 的处理性能的支持有了极大的提高，而 InnoDB 1.2 版本又支持多个 purge 线程，以及将刷新操作从 master thread 中分离出来。因此，若用户的 CPU 支持多核，InnoDB 的版本应该选择1.1 或更高版本。

另外，如果 CPU 是多核的，可以通过修改参数 innodb_read_io_threads 和 innodb_write_io_threads 来增大 IO 的线程，这样也能更充分有效地利用 CPU 的多核性能。

在当前的 MySQL 数据库版本中，一条 SQL 查询语句只能在一个 CPU 中工作，并不支持多 CPU 的处理。OLTP 的数据库应用操作一般都很简单，因此对 OLTP 应用的影响并不是很大。但是，多个 CPU 或多核 CPU 对处理大并发量的请求还是会有帮助。

内存的重要性​

内存的大小是最能直接反映数据库的性能。InnoDB 存储引擎既缓存数据，又缓存索引，并且将它们缓存于一个很大的缓冲池中，即 InnoDB Buffer Pool。

因此，内存的大小直接影响了数据库的性能。

在上述测试中，数据和索引总大小为 18GB，然后将缓冲池的大小分别设为 2GB、4GB、6GB、 8GB、 10GB、 12GB、 14GB、16GB、 18GB、 20GB、 22GB，再进行 sysbench 的测试。可以发现，随着缓冲池的增大，测试结果TPS （Transaction Per Second）会线性增长。

当缓冲池增大到 20GB 和 22GB 时，数据库的性能有了极大的提高，因为这时缓冲池的大小已经大于数据文件本身的大小，所有对数据文件的操作都可以在内存中进行。因此这时的性能应该是最优的，再调大缓冲池并不能再提高数据库的性能。

所以，应该在开发应用前预估 “活跃” 数据库的大小是多少，并以此确定数据库服务器内存的大小。当然，要使用更多的内存还必须使用 64 位的操作系统。

硬盘对数据库性能的影响​

机械硬盘​

当前大多数数据库使用的都是传统的机械硬盘。机械硬盘的技术目前已非常成熟，在服务器领域一般使用 SAS 或 SATA 接口的硬盘。服务器机械硬盘开始向小型化转型，目前大部分使用2.5寸的SAS机械硬盘。

机械硬盘有两个重要的指标：一个是寻道时间，另一个是转速。

当前服务器机械硬盘的寻道时间已经能够达到 3ms，转速为 15 000RPM（rotate per minute）。传统机械硬盘最大的问题在于读写磁头，读写磁头的设计使硬盘可以不再像磁带一样，只能进行顺序访问，而是可以随机访问。

但是，机械硬盘的访问需要耗费长时间的磁头旋转和定位来查找，因此顺序访问的速度要远高于随机访问。传统关系数据库的很多设计也都是在尽量充分地利用顺序访问的特性。

通常来说，可以将多块机械硬盘组成 RAID 来提高数据库的性能，也可以将数据文件分布在不同硬盘上来达到访问负载的均衡。

固态硬盘​

固态硬盘，更准确地说是基于闪存的固态硬盘，是近几年出现的一种新的存储设备，其内部由闪存（Flash Memory）组成。因为闪存的低延迟性、低功耗，以及防震性，闪存设备已在移动设备上得到了广泛的应用。

企业级应用一般使用固态硬盘， 通过并联多块闪存来进一步提高数据传输的吞吐量。传统的存储服务提供商 EMC 公司已经开始提供基于闪存的固态硬盘的 TB 级别存储解决方案。数据库厂商 Oracle 公司最近也开始提供绑定固态硬盘的 Exadata 服务器。

不同于传统的机械硬盘，闪存是一个完全的电子设备，没有传统机械硬盘的读写磁头。因此，固态硬盘不需要像传统机械硬盘一样，需要耗费大量时间的磁头旋转和定位来查找数据，所以固态硬盘可以提供一致的随机访问时间。 固态硬盘这种对数据的快速读写和定位特性是值得研究的。

另一方面，闪存中的数据是不可以更新的，只能通过扇区（sector）的覆盖重写，而在覆盖重写之前，需要执行非常耗时的擦除（erase）操作。擦除操作不能在所含数据的扇区上完成，而需要在删除整个被称为擦除块的基础上完成，这个擦除块的尺寸大于扇区的大小，通常为 128KB 或者 256KB。此外，每个擦除块有擦写次数的限制。已经有一些算法来解决这个问题。但是对于数据库应用，需要认真考虑固态硬盘在写人方面存在的问题。

因为存在上述写入方面的问题，闪存提供的读写速度是非对称的。读取速度要远快于写入的速度，因此对于固态硬盘在数据库中的应用，应该好好利用其读取的性能，避免过多的写人操作。

数据库结构优化​

一个好的数据库设计方案对于数据库的性能往往会起到事半功倍的效果。

需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

将字段很多的表分解成多个表​

对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。

因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

增加中间表​

对于需要经常联合查询的表，可以建立中间表以提高查询效率。

通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。

增加冗余字段​

设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询速度。

表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。

注意：

冗余字段的值在一个表中修改了，就要想办法在其他表中更新，否则就会导致数据不一致的问题。

MySQL 数据库 cpu 飙升到500%的话他怎么处理？

主键使用自增 ID 还是 UUID？​

推荐使用自增ID，不要使用UUID。

因为在 InnoDB 存储引擎中，主键索引是作为聚簇索引存在的，也就是说，主键索引的 B+ 树叶子节点上存储了主键索引以及全部的数据（按照顺序），如果主键索引是自增 ID，那么只需要不断向后排列即可，如果是 UUID，由于到来的 ID 与原来的大小不确定，会造成非常多的数据插入，数据移动，然后导致 产生很多的内存碎片，进而造成插入性能的下降。

总之，在数据量大一些的情况下，用自增主键性能会好一些。

关于主键是聚簇索引，如果没有主键，InnoDB 会选择一个唯一键来作为聚簇索引，如果没有唯一键，会生成一个隐式的主键。